﻿-- Function: pl_function.generar_cuenta(character varying, character varying, character varying, character varying)

-- DROP FUNCTION pl_function.generar_cuenta(character varying, character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION pl_function.generar_cuenta(p_mhresum character varying, p_mperson character varying, p_vhostnm character varying, p_vusernm character varying)
  RETURNS integer AS
$BODY$
declare
	p_idsigma integer;
begin
	create temporary table mestcta_temp(
		  cidpred character(10)
		, ctiprec character(10)
		, ninsolu numeric(10, 2)
		, vhostnm character varying(25)
		, vusernm character varying(25)
		, ddatetm timestamp without time zone
		, cperanio character varying(4)
		, ntipo integer
		, mpredio character(10)
	) on commit drop;

	insert into mestcta_temp(cidpred, ctiprec, ninsolu, vhostnm, vusernm, ddatetm, cperanio, ntipo, mpredio)
	select cidpred, ctiprec, ninsolu, vhostnm, vusernm, ddatetm, cperiod, ntipo, mpredio
	from (
		select a.idsigma cidpred, '0000000279' ctiprec, nbarrid ninsolu, p_vhostnm vhostnm, p_vusernm vusernm, now() ddatetm, cperiod, 1 ntipo, mpredio
		from registro.dpredio a inner join registro.vpredio b on a.idsigma=b.dpredio and mhresum=p_mhresum
		union all
		select a.idsigma cidpred, '0000000283' ctiprec, nparque ninsolu, p_vhostnm vhostnm, p_vusernm vusernm, now() ddatetm, cperiod, 1 ntipo, mpredio
		from registro.dpredio a inner join registro.vpredio b on a.idsigma=b.dpredio and mhresum=p_mhresum
		union all
		select a.idsigma cidpred, '0000007267' ctiprec, nrecole ninsolu, p_vhostnm vhostnm, p_vusernm vusernm, now() ddatetm, cperiod, 1 ntipo, mpredio
		from registro.dpredio a inner join registro.vpredio b on a.idsigma=b.dpredio and mhresum=p_mhresum
		union all
		select a.idsigma cidpred, '0000008509' ctiprec, nserena ninsolu, p_vhostnm vhostnm, p_vusernm vusernm, now() ddatetm, cperiod, 1 ntipo, mpredio
		from registro.dpredio a inner join registro.vpredio b on a.idsigma=b.dpredio and mhresum=p_mhresum
		union all
		select null cidpred, '0000000273' ctiprec, nimpanu ninsolu, p_vhostnm vhostnm, p_vusernm vusernm, now() ddatetm, cperiod, 1 ntipo, null mpredio
		from registro.dresumn a where mhresum=p_mhresum
	) a;

	insert into mestcta_temp(cidpred, ctiprec, ninsolu, vhostnm, vusernm, ddatetm, cperanio, ntipo, mpredio)
	select a.idsigma cidpred, b.ctiprec, sum(b.imp_insol) ninsolu, p_vhostnm vhostnm, p_vusernm vusernm, now() ddatetm, b.cperanio, -1 ntipo, b.mpredio
	from registro.dpredio a 
		inner join registro.vpredio c on a.idsigma=c.dpredio
		inner join tesoreria.mestcta b on a.mpredio=b.mpredio and c.cperiod=b.cperanio
	where a.mhresum=p_mhresum
		and b.cidpers=p_mperson
		and b.ctiprec in('0000000279', '0000000283', '0000007267', '0000008509', '0000000273')
	group by a.idsigma, b.ctiprec, b.cperanio, b.mpredio;
	
	select coalesce(max(idsigma)::numeric(18, 0), 0) into p_idsigma from tesoreria.mestcta; 
	
	insert into tesoreria.mestcta(
		  idsigma, cidpers, cidpred
		, ctiping, ctiprec, cperiod
		, ncantid, imp_insol, fact_reaj, imp_reaj
		, fact_mora, imp_mora, costo_emis
		, dfecven, dfecpag, vobserv
		, nestado, vusernm, vhostnm
		, ddatetm, cperanio, mhresum
	) select lpad(((row_number() over(order by a.idsigma)) + p_idsigma)::varchar, 10, '0') idsigma
		, $2 cidpers
		, b.cidpred
		, a.ctiping
		, a.ctiprec
		, a.cperiod
		, 1 ncantid
		, trunc(ninsolu / nnrocuo, 2) imp_insol
		, 0 fact_reaj
		, 0 imp_reaj
		, 0 fact_mora
		, 0 imp_mora
		, 0 costo_emis
		, a.dfecven
		, null dfecpag
		, '' vobserv
		, 0 nestado
		, b.vusernm
		, b.vhostnm
		, now() ddatetm
		, a.cperanio
		, $1
	from registro.pestcta a inner join (
		select cidpred, ctiprec, sum(ninsolu * ntipo) ninsolu
			, vhostnm, vusernm, ddatetm
			, row_number() over(order by cperanio, cidpred) idsigma, cperanio
		from  mestcta_temp
		group by cidpred, ctiprec, vhostnm, vusernm, ddatetm, cperanio
	) b on a.cperanio=b.cperanio and a.ctiprec=b.ctiprec;

	update registro.mhresum set nrecalc=1 where idsigma=p_mhresum;
	return 1;
exception
when sqlstate '57014' then
	raise notice 'Error';
	return 0;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION pl_function.generar_cuenta(character varying, character varying, character varying, character varying)
  OWNER TO postgres;
GRANT EXECUTE ON FUNCTION pl_function.generar_cuenta(character varying, character varying, character varying, character varying) TO public;
GRANT EXECUTE ON FUNCTION pl_function.generar_cuenta(character varying, character varying, character varying, character varying) TO postgres;
GRANT EXECUTE ON FUNCTION pl_function.generar_cuenta(character varying, character varying, character varying, character varying) TO desarrollo;

-- select * from registro.vpredio limit 10
-- select * from tesoreria.mestcta limit 10